package hx;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelChange {
	

    public static void main(String[] args) {
		File file = new File("D:\\users\\Desktop\\新建文件夹\\天爱儿科门诊诊所患者病历-201605-201711.xls");
		try {
			List<String[]> list = read2(file);
			Map<String,String> map = new HashMap<>();
			for (int i = 1; i < list.size(); i++) {
				String[] strings = list.get(i);
				map.put(strings[2], strings[1]);
			}
			File file1 = new File("D:\\users\\Desktop\\新建文件夹\\客户表(1).xls");
			List<String[]> result = new ArrayList<>();
			List<String[]> list1 = read2(file1);
			result.add(list1.get(0));
			for (int i = 1; i < list1.size(); i++) {
				String[] strs = list1.get(i);
				strs[0] = map.get(strs[1])==null?"":map.get(strs[1]);
				result.add(strs);
			}
			writeXLSXFileByString("D:\\users\\Desktop\\新建文件夹\\333.xlsx",result);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 读原xls表格
	 * @param f
	 * @return	Map<String, List<String[]>>
	 * 			String--->sheet内容	List<String[]>--->表格内容
	 * @throws IOException
	 */
	public static List<String[]> read2(File f) throws IOException {
		Map<String, List<String[]>> map = new HashMap<String, List<String[]>>();
		InputStream stream = new FileInputStream(f);
		Workbook wb = null;
		if (f.getAbsolutePath().endsWith("xls")) {
			wb = new HSSFWorkbook(stream);
		} else if (f.getAbsolutePath().endsWith("xlsx")) {
			wb = new XSSFWorkbook(stream);
		} else {
			System.out.println("您输入的excel格式不正确");
		}
		Sheet sh = (Sheet) wb.getSheetAt(0);
		Row titleRow = sh.getRow(0);
		//标题列数
		int titleColNum = titleRow.getLastCellNum();
		//标题
		String[] title = new String[titleColNum];
		for (int j = 0; j < titleColNum; j++) {
			title[j] = getCellFormatValue(titleRow.getCell((short) j));
		}
		
		//读取内容
		int rowNum = sh.getLastRowNum();
		List<String[]> list = new ArrayList<String[]>();
		//一行内容
		list.add(title);
		//内容从第二行开始
		for (int i = 1; i <= rowNum; i++) {
			Row row = sh.getRow(i);
			String[] body = new String[titleColNum];
			for (int j = 0; j < titleColNum; j++) {
				if(row.getCell(j)!=null){
					row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
					body[j] = row.getCell(j).getStringCellValue();
				}
			}
			list.add(body);
		}
		
		return list;
	}
	
	public static File writeXLSXFileByString(String path, List<String[]> list
		) throws IOException {

		String excelFileName = path;// name of excel file

		String sheetName = "Sheet1";// name of sheet

		SXSSFWorkbook wb = new SXSSFWorkbook(4000);

		SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(sheetName);
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setWrapText(true);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		for (int r = 0; r < list.size(); r++) {
			SXSSFRow row = (SXSSFRow) sheet.createRow(r);
			String[] str = list.get(r);
			for (int i = 0; i < str.length; i++) {
				Cell cell = row.createCell(i);
				cell.setCellValue(str[i]);
			}
			
		}

		File f = new File(excelFileName);
		if (!f.exists()) {
			f.createNewFile();
		}
		FileOutputStream fileOut = new FileOutputStream(excelFileName);
		wb.write(fileOut);
		fileOut.flush();
		fileOut.close();
		return f;
	}
	
	
    /**
     * 根据HSSFCell类型设置数据
     * @param cell
     * @return
     */
    private static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case HSSFCell.CELL_TYPE_NUMERIC:
            case HSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型则，转化为Data格式
                    
                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                    //cellvalue = cell.getDateCellValue().toLocaleString();
                    
                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                    
                }
                // 如果是纯数字
                else {
                    // 取得当前Cell的数值
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            // 如果当前Cell的Type为STRIN
            case HSSFCell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }
    
    

}
